package jsyf.oa.dao.intentCustomerReport;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;

import jsyf.oa.entity.report.mineCustomer.CustomerReportEntity;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.List;

import jsyf.oa.idao.BaseDao;
import jsyf.oa.ientity.IBaseEntity;

/**
 * Created by 92852 on 2018/8/7.
 */
@Repository
public class IntentCustomerReportDao extends BaseDao {

    public IntentCustomerReportDao() {
        super.iBaseEntity = new CustomerReportEntity();
    }

    public List<CustomerReportEntity> getEntityListByCondition(String condition, Object[] params) {
        List<JSONObject> jsonList = getJSONListByCondition(condition, params);
        List<CustomerReportEntity> entityList = new ArrayList<>();
        for (int i = 0; i < jsonList.size(); i++) {
            CustomerReportEntity entity = (CustomerReportEntity) convertJson2Entity(jsonList.get(i));
            entityList.add(entity);
        }
        return entityList;
    }

    @Override
    public IBaseEntity convertJson2Entity(JSONObject json) {
        return JSON.toJavaObject(json, CustomerReportEntity.class);
    }

    public List<JSONObject> getGroupByUserId(String companyId) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "t_sys_company.`NAME` AS companyName,\n" +
                "t_sys_company.ID AS companyId,\n" +
                "COUNT(1) AS allNum,\n" +
                "COUNT(case when t_wrt_customer.BUY_DATE='一周内' then t_wrt_customer.BUY_DATE end ) AS weekNum,\n" +
                "COUNT(case when t_wrt_customer.BUY_DATE='一月内' then t_wrt_customer.BUY_DATE end ) AS MonNum,\n" +
                "COUNT(case when t_wrt_customer.BUY_DATE='一月以上' then t_wrt_customer.BUY_DATE end ) AS MoreNum\n" +
                "FROM\n" +
                "t_wrt_customer\n" +
                "LEFT JOIN t_sys_user ON t_sys_user.ID=t_wrt_customer.CREATE_USER_ID\n" +
                "LEFT JOIN t_sys_role ON t_sys_role.ID=t_sys_user.ROLE_ID\n" +
                "LEFT JOIN t_sys_depart ON t_sys_depart.ID=t_sys_role.DEPART_ID\n" +
                "LEFT JOIN t_sys_company ON t_sys_company.ID=t_sys_depart.COMPANY_ID\n" +
                "WHERE 1=1\n" +
                "AND t_sys_company.ID in(" + companyId + ")\n" +
                "AND t_sys_user.JOB_STATE<>1\n" +
                "AND (t_wrt_customer.BUY_DATE IS NOT NULL\n" +
                "OR t_wrt_customer.BUY_TYPE IS NOT NULL\n" +
                "OR t_wrt_customer.INTENTION_MODEL IS NOT NULL)\n" +
                "AND t_wrt_customer.INTENTION_MODEL NOT in ('无','暂无')\n "+
                "AND t_wrt_customer.OTHER_CONDITION IS NOT NULL\n" +
                "AND  t_wrt_customer.`NAME` IS NOT NULL\n" +
                "AND  t_wrt_customer.CREATE_USER_ID IS NOT NULL\n" +
                "AND t_sys_company.DEL_FLAG=0\n" +
                "AND t_sys_depart.DEL_FLAG=0\n" +
                "AND t_sys_role.DEL_FLAG=0\n" +
                "GROUP BY t_sys_company.ID\n" +
                "ORDER BY t_sys_company.SORT_NO\n" +
                "\n");
        List<JSONObject> result = super.queryForJsonList(sql.toString());
        return result;
    }

    public List<JSONObject> getAllCustomersByCompanyId(String condition, int requestPage, int pageSize) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "t_sys_company.`NAME` AS companyName,\n" +
                "t_sys_company.ID AS companyId,\n" +
                "t_sys_role_name.`NAME` AS createRoleName,\n" +
                "t_sys_user.`NAME` AS createUserName,\n" +
                "t_wrt_customer.CREATE_USER_ID AS createUserId,\n" +
                "t_sys_user_parent.ID AS createParentId,\n" +
                "t_sys_user_parent.`NAME` AS createParentName,\n" +
                "t_wrt_customer.ID AS customerId,\n" +
                "t_wrt_customer.`NAME` AS customerName,\n" +
                "t_wrt_customer.TEL AS tel,\n" +
                "t_wrt_customer.CUSTOMER_TYPE AS customerType,\n" +
                "t_wrt_customer.CUSTOMER_CREDIT AS customerCredit,\n" +
                "concat_ws('-',t_wrt_customer.CITY_NAME, t_wrt_customer.COUNTY_NAME) as customerlocation,\n" +
                "t_wrt_customer.BUY_DATE AS buyDate,\n" +
                "t_wrt_customer.BUY_TYPE AS buyType,\n" +
                "t_wrt_customer.INTENTION_MODEL AS intentionModel,\n" +
                "t_wrt_customer.INTENTION_FLAG AS intentionFlag,\n" +
                "t_wrt_customer.THROW_REASON AS throwReason,\n" +
                "t_wrt_customer.INTENTION_TIME AS intentionTime,\n" +
                "t_wrt_customer.OTHER_CONDITION AS otherCondition,\n" +
                "t_wrt_customer.CHANCE AS chance,\n" +
                "t_wrt_customer.BUSINESS_DATE AS businessDate\n" +
                "FROM\n" +
                "t_wrt_customer\n" +
                "LEFT JOIN t_sys_user ON t_sys_user.ID=t_wrt_customer.CREATE_USER_ID\n" +
                "LEFT JOIN t_sys_role ON t_sys_role.ID=t_sys_user.ROLE_ID\n" +
                "LEFT JOIN t_sys_depart ON t_sys_depart.ID=t_sys_role.DEPART_ID\n" +
                "LEFT JOIN t_sys_company ON t_sys_company.ID=t_sys_depart.COMPANY_ID\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role_name.ID=t_sys_role.ROLE_NAME_ID\n" +
                "LEFT JOIN t_sys_user AS t_sys_user_parent ON t_sys_user_parent.ID=t_sys_user.PARENT_ID\n" +
                "WHERE 1=1\n");
        sql.append(condition);
        sql.append(
                "AND t_sys_user.JOB_STATE<>1\n" +
                        "AND (t_wrt_customer.BUY_DATE IS NOT NULL\n" +
                        "OR t_wrt_customer.BUY_TYPE IS NOT NULL\n" +
                        "OR t_wrt_customer.INTENTION_MODEL IS NOT NULL)\n" +
                        "AND t_wrt_customer.INTENTION_MODEL NOT in ('无','暂无')\n "+
                        "AND t_wrt_customer.OTHER_CONDITION IS NOT NULL\n" +
                        "AND  t_wrt_customer.`NAME` IS NOT NULL\n" +
                        "AND  t_wrt_customer.CREATE_USER_ID IS NOT NULL\n" +
                        "AND t_sys_company.DEL_FLAG=0\n" +
                        "AND t_sys_depart.DEL_FLAG=0\n" +
                        "AND t_sys_role.DEL_FLAG=0\n" +
                        "GROUP BY t_wrt_customer.ID\n" +
                        "ORDER BY t_sys_role_name.SORT_NO,t_wrt_customer.CREATE_TIME");
        super.appendPageSql(sql, requestPage, pageSize);
        List<JSONObject> result = super.queryForJsonList(sql.toString());
        return result;
    }

    public List<JSONObject> getPersonalByUserId(String condition) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "t_sys_company.`NAME` AS companyName,\n" +
                "t_sys_company.ID AS companyId,\n" +
                "t_sys_role_name.`NAME` AS createRoleName,\n" +
                "t_sys_user.`NAME` AS createUserName,\n" +
                "t_wrt_customer.CREATE_USER_ID AS createUserId,\n" +
                "t_sys_user_parent.ID AS createParentId,\n" +
                "t_sys_user_parent.`NAME` AS createParentName,\n" +
                "t_wrt_customer.ID AS customerId,\n" +
                "t_wrt_customer.`NAME` AS customerName,\n" +
                "t_wrt_customer.TEL AS tel,\n" +
                "t_wrt_customer.CUSTOMER_TYPE AS customerType,\n" +
                "t_wrt_customer.CUSTOMER_CREDIT AS customerCredit,\n" +
                "concat_ws('-',t_wrt_customer.CITY_NAME, t_wrt_customer.COUNTY_NAME) as customerlocation,\n" +
                "t_wrt_customer.BUY_DATE AS buyDate,\n" +
                "t_wrt_customer.BUY_TYPE AS buyType,\n" +
                "t_wrt_customer.INTENTION_MODEL AS intentionModel,\n" +
                "t_wrt_customer.INTENTION_FLAG AS intentionFlag,\n" +
                "t_wrt_customer.THROW_REASON AS throwReason,\n" +
                "t_wrt_customer.INTENTION_TIME AS intentionTime,\n" +
                "t_wrt_customer.OTHER_CONDITION AS otherCondition,\n" +
                "t_wrt_customer.CHANCE AS chance,\n" +
                "t_wrt_customer.BUSINESS_DATE AS businessDate\n" +
                "FROM\n" +
                "t_wrt_customer\n" +
                "LEFT JOIN t_sys_user ON t_sys_user.ID=t_wrt_customer.CREATE_USER_ID\n" +
                "LEFT JOIN t_sys_role ON t_sys_role.ID=t_sys_user.ROLE_ID\n" +
                "LEFT JOIN t_sys_depart ON t_sys_depart.ID=t_sys_role.DEPART_ID\n" +
                "LEFT JOIN t_sys_company ON t_sys_company.ID=t_sys_depart.COMPANY_ID\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role_name.ID=t_sys_role.ROLE_NAME_ID\n" +
                "LEFT JOIN t_sys_user AS t_sys_user_parent ON t_sys_user_parent.ID=t_sys_user.PARENT_ID\n" +
                "WHERE 1=1\n");
        sql.append(condition);
        sql.append("AND t_sys_user.JOB_STATE<>1\n" +
                "AND (t_wrt_customer.BUY_DATE IS NOT NULL\n" +
                "OR t_wrt_customer.BUY_TYPE IS NOT NULL\n" +
                "OR t_wrt_customer.INTENTION_MODEL IS NOT NULL)\n" +
                "AND t_wrt_customer.INTENTION_MODEL NOT in ('无','暂无')\n "+
                "AND t_wrt_customer.OTHER_CONDITION IS NOT NULL\n" +
                "AND  t_wrt_customer.`NAME` IS NOT NULL\n" +
                "AND  t_wrt_customer.CREATE_USER_ID IS NOT NULL\n" +
                "AND t_sys_company.DEL_FLAG=0\n" +
                "AND t_sys_depart.DEL_FLAG=0\n" +
                "AND t_sys_role.DEL_FLAG=0\n" +
                "GROUP BY t_wrt_customer.ID\n" +
                "ORDER BY t_sys_role_name.SORT_NO,t_wrt_customer.CREATE_TIME");
        List<JSONObject> result = super.queryForJsonList(sql.toString());
        return result;
    }

    public List<JSONObject> getBigRegionByUserId(String condition) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "t_sys_company.`NAME` AS companyName,\n" +
                "t_sys_company.ID AS companyId,\n" +
                "t_sys_depart.ID AS departId,\n" +
                "t_sys_depart.`NAME` AS departName,\n" +
                "t_sys_role_name.`NAME` AS createRoleName,\n" +
                "t_sys_user.`NAME` AS createUserName,\n" +
                "t_wrt_customer.CREATE_USER_ID AS createUserId,\n" +
                "t_sys_user_parent.ID AS createParentId,\n" +
                "t_sys_user_parent.`NAME` AS createParentName,\n" +
                "t_wrt_customer.ID AS customerId,\n" +
                "t_wrt_customer.`NAME` AS customerName,\n" +
                "t_wrt_customer.TEL AS tel,\n" +
                "t_wrt_customer.CUSTOMER_TYPE AS customerType,\n" +
                "t_wrt_customer.CUSTOMER_CREDIT AS customerCredit,\n" +
                "concat_ws('-',t_wrt_customer.CITY_NAME, t_wrt_customer.COUNTY_NAME) as customerlocation,\n" +
                "t_wrt_customer.BUY_DATE AS buyDate,\n" +
                "t_wrt_customer.BUY_TYPE AS buyType,\n" +
                "t_wrt_customer.INTENTION_MODEL AS intentionModel,\n" +
                "t_wrt_customer.INTENTION_FLAG AS intentionFlag,\n" +
                "t_wrt_customer.THROW_REASON AS throwReason,\n" +
                "t_wrt_customer.INTENTION_TIME AS intentionTime,\n" +
                "t_wrt_customer.OTHER_CONDITION AS otherCondition\n" +
                "FROM\n" +
                "t_wrt_customer\n" +
                "LEFT JOIN t_sys_user ON t_sys_user.ID=t_wrt_customer.CREATE_USER_ID\n" +
                "LEFT JOIN t_sys_role ON t_sys_role.ID=t_sys_user.ROLE_ID\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role_name.ID=t_sys_role.ROLE_NAME_ID\n" +
                "LEFT JOIN t_sys_depart ON t_sys_depart.ID=t_sys_role.DEPART_ID\n" +
                "LEFT JOIN t_sys_company ON t_sys_company.ID=t_sys_depart.COMPANY_ID\n" +
                "LEFT JOIN t_sys_user AS t_sys_user_parent ON t_sys_user_parent.ID=t_sys_user.PARENT_ID\n" +
                "WHERE 1=1\n" +
                "AND t_sys_user.JOB_STATE<>1\n" +
                "AND (t_wrt_customer.BUY_DATE IS NOT NULL\n" +
                "OR t_wrt_customer.BUY_TYPE IS NOT NULL\n" +
                "OR t_wrt_customer.INTENTION_MODEL IS NOT NULL)\n" +
                "AND t_wrt_customer.INTENTION_MODEL NOT in ('无','暂无')\n "+
                "AND t_wrt_customer.OTHER_CONDITION IS NOT NULL\n" +
                "AND  t_wrt_customer.`NAME` IS NOT NULL\n" +
                "AND  t_wrt_customer.CREATE_USER_ID IS NOT NULL\n" +
                "AND t_sys_company.DEL_FLAG=0\n" +
                "AND t_sys_depart.DEL_FLAG=0\n" +
                "AND t_sys_role.DEL_FLAG=0\n");
        sql.append(condition);
        sql.append("AND `t_sys_role_name`.`FRONT_FLAG` = 1\n" +
                "GROUP BY t_wrt_customer.ID\n" +
                "ORDER BY t_sys_role_name.SORT_NO,t_sys_user.SORT_NO,t_wrt_customer.CREATE_TIME\n");
        List<JSONObject> result = super.queryForJsonList(sql.toString());
        return result;
    }

    public List<JSONObject> getCompanyByUserId(String condition) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "t_sys_company.`NAME` AS companyName,\n" +
                "t_sys_company.ID AS companyId,\n" +
                "t_sys_role_name.`NAME` AS createRoleName,\n" +
                "t_sys_user.`NAME` AS createUserName,\n" +
                "t_wrt_customer.CREATE_USER_ID AS createUserId,\n" +
                "t_sys_user_parent.ID AS createParentId,\n" +
                "t_sys_user_parent.`NAME` AS createParentName,\n" +
                "t_wrt_customer.ID AS customerId,\n" +
                "t_wrt_customer.`NAME` AS customerName,\n" +
                "t_wrt_customer.TEL AS tel,\n" +
                "t_wrt_customer.CUSTOMER_TYPE AS customerType,\n" +
                "t_wrt_customer.CUSTOMER_CREDIT AS customerCredit,\n" +
                "concat_ws('-',t_wrt_customer.CITY_NAME, t_wrt_customer.COUNTY_NAME) as customerlocation,\n" +
                "t_wrt_customer.BUY_DATE AS buyDate,\n" +
                "t_wrt_customer.BUY_TYPE AS buyType,\n" +
                "t_wrt_customer.INTENTION_MODEL AS intentionModel,\n" +
                "t_wrt_customer.INTENTION_FLAG AS intentionFlag,\n" +
                "t_wrt_customer.THROW_REASON AS throwReason,\n" +
                "t_wrt_customer.INTENTION_TIME AS intentionTime,\n" +
                "t_wrt_customer.OTHER_CONDITION AS otherCondition,\n" +
                "t_wrt_customer.CHANCE AS chance,\n" +
                "t_wrt_customer.BUSINESS_DATE AS businessDate\n" +
                "FROM\n" +
                "t_wrt_customer\n" +
                "LEFT JOIN t_sys_user ON t_sys_user.ID=t_wrt_customer.CREATE_USER_ID\n" +
                "LEFT JOIN t_sys_role ON t_sys_role.ID=t_sys_user.ROLE_ID\n" +
                "LEFT JOIN t_sys_depart ON t_sys_depart.ID=t_sys_role.DEPART_ID\n" +
                "LEFT JOIN t_sys_company ON t_sys_company.ID=t_sys_depart.COMPANY_ID\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role_name.ID=t_sys_role.ROLE_NAME_ID\n" +
                "LEFT JOIN t_sys_user AS t_sys_user_parent ON t_sys_user_parent.ID=t_sys_user.PARENT_ID\n" +
                "WHERE 1=1\n");
        sql.append(condition);
        sql.append(
                "AND t_sys_user.JOB_STATE<>1\n" +
                        "AND (t_wrt_customer.BUY_DATE IS NOT NULL\n" +
                        "OR t_wrt_customer.BUY_TYPE IS NOT NULL\n" +
                        "OR t_wrt_customer.INTENTION_MODEL IS NOT NULL)\n" +
                        "AND t_wrt_customer.INTENTION_MODEL NOT in ('无','暂无')\n "+
                        "AND t_wrt_customer.OTHER_CONDITION IS NOT NULL\n" +
                        "AND  t_wrt_customer.`NAME` IS NOT NULL\n" +
                        "AND  t_wrt_customer.CREATE_USER_ID IS NOT NULL\n" +
                        "AND t_sys_company.DEL_FLAG=0\n" +
                        "AND t_sys_depart.DEL_FLAG=0\n" +
                        "AND t_sys_role.DEL_FLAG=0\n" +
                        "GROUP BY t_wrt_customer.ID\n" +
                        "ORDER BY  t_sys_depart.SORT_NO,t_sys_role_name.SORT_NO,t_sys_user.SORT_NO,t_wrt_customer.CREATE_TIME");
        List<JSONObject> result = super.queryForJsonList(sql.toString());
        return result;
    }

    public  Integer getCountGroupCustomerList(String condition){
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "IFNULL(COUNT(1),0) AS count\n" +
                "FROM\n" +
                "t_wrt_customer\n" +
                "LEFT JOIN t_sys_user ON t_sys_user.ID=t_wrt_customer.CREATE_USER_ID\n" +
                "LEFT JOIN t_sys_role ON t_sys_role.ID=t_sys_user.ROLE_ID\n" +
                "LEFT JOIN t_sys_depart ON t_sys_depart.ID=t_sys_role.DEPART_ID\n" +
                "LEFT JOIN t_sys_company ON t_sys_company.ID=t_sys_depart.COMPANY_ID\n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role_name.ID=t_sys_role.ROLE_NAME_ID\n" +
                "LEFT JOIN t_sys_user AS t_sys_user_parent ON t_sys_user_parent.ID=t_sys_user.PARENT_ID\n" +
                "WHERE 1=1\n" +
                "AND t_sys_user.JOB_STATE<>1\n" +
                "AND (t_wrt_customer.BUY_DATE IS NOT NULL\n" +
                "OR t_wrt_customer.BUY_TYPE IS NOT NULL\n" +
                "OR t_wrt_customer.INTENTION_MODEL IS NOT NULL)\n" +
                "AND t_wrt_customer.INTENTION_MODEL NOT in ('无','暂无')\n "+
                "AND t_wrt_customer.OTHER_CONDITION IS NOT NULL\n" +
                "AND  t_wrt_customer.`NAME` IS NOT NULL\n" +
                "AND  t_wrt_customer.CREATE_USER_ID IS NOT NULL\n" +
                "AND t_sys_company.DEL_FLAG=0\n" +
                "AND t_sys_depart.DEL_FLAG=0\n" +
                "AND t_sys_role.DEL_FLAG=0\n");
        sql.append(condition);
        String count = super.queryForString(sql.toString());
        return Integer.parseInt(count);
    }

}

